public class Test{
	public Connection getConnection(){	//获取数据库连接
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&character=utf8&serverTimezone=UTC";
		String username = "root";
		String password = "123456";
		
		Class.forName("com.jdbc.mysql.Driver");
		
		return DriverManager.getConnection(url,username,password);
	}
	
	//根据id和密码进行身份查验,身份正确返回true,否则返回false
	public boolean identificationCheck(int id, String pwd){
		//通过用户名和密码验证用户身份
		//根据用户id从数据库中查找用户信息
		Connection connection = getConnection();
		String sql = "select uid,pwd from t_user where uid=?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setInt(1,id);
		ResultSet resultSet = statement.excuteQuery();
		//用户存在
		if(resultSet.next()){	
			String dbPwd = resultSet.getString("pwd");	//从数据库中获取密码
			pwd = MD5(pwd);	//将明文密码通过MD5算法加密(数据库中存储的密码经过加密)
			//密码正确,身份验证通过
			if(pwd.equals(dbPwd)){
				return true;
			}
		}
		
		return false;
	}
	
	//查询用户钱包余额接口实现
	//查询正常返回余额,查询异常返回-1
	public long checkBalance(int uid,String pwd){	
		if(identificationCheck(uid,pwd)){	//用户身份验证
			Connection connection = getConnection();
			//从数据库中查询指定id的用户钱包余额
			String sql = "select balance from t_wallet where uid = ?"
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setInt(1,uid);
			ResultSet resultSet = statement.excuteQuery();
			
			if(resultSet.next()){
				//获取余额
				long balance =  resultSet.getLong("balance");
				//关闭资源
				resultSet.close();
				prepareStatement.close();
				connection.close();
			
				return balance;
			}
			resultSet.close();
			prepareStatement.close();
			connection.close();
		}
		return -1;
	}
	
	//此方法实现转账,是消费和退款功能的核心
	//uid为付款方id,bid为收款方id
	public boolean transferAcounts(int uid,int bid,int money){
		//余额不足,转账失败
		long balance = checkBalance(uid);
		if(balance < money)
			return false;
		
		//余额充足,进行转账
		Connection connection = getConnection();
		String sql = "update t_wallet set balance = ? where uid = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		try{
			//关闭自动提交(开启事务)
			connection.setAutoCommit(false);
			//付款方付款
			statement.setLong(1,balance - money);	
			statement.setInt(2,uid);
			statement.addBatch();//加入批命令
			//收款方收款
			statement.setLong(1,balance + money);	
			statement.setInt(2,bid);
			statement.addBatch();//加入批命令
			//执行批命令
			statement.execute();
			
			//记录流水
			//master_id为交易流水的主方id,master_id为客方id
			sql = "insert into t_blotter(master_id,client_id,money,date) value(?,?,?,?)";
			//获取当前时间并格式化
			Date date = new Date();
			SimpleDateFormat format = new SimpleDateFormat("yy-MM-dd hh:mm:ss");
			String ftDate = format.format(date);
			//记录流水信息
			statement = connection.prepareStatement(sql);
			//付款方流水
			statement.setInt(1,uid);
			statement.setInt(2,bid);
			statement.setInt(3,-money);
			statement.setString(4,ftDate);
			statement.addBatch();
			//收款方流水
			statement.setInt(1,bid);
			statement.setInt(2,uid);
			statement.setInt(3,money);
			statement.setString(4,ftDate);
			statement.addBatch();
			//执行批命令
			statement.execute();
			
			//提交事务
			connection.commit();
		}catch (Exception e){
			//回滚事务
			connection.rollback();
			//转账失败
			return false;
		}finally {
			connection.setAutoCommit(true);
			statement.close();
			connection.close();
		}
	
		//转账成功
		return true;
	}
	
	//用户消费100元接口实现
	//uid为消费者id,pwd为消费者密码,bid为商家id
	//消费成功返回true,否则返回false
	public boolean consumeOneHundred(int uid,String pwd,int bid){
		if(identificationCheck(uid,pwd)){	//用户身份验证通过
			return transferAcounts(uid,bid,100);	//消费100元
		}
		return false;
	}
	
	//用户退款20元接口实现
	//uid为消费者id,pwd为消费者密码,bid为商家id
	//退款成功返回true,否则返回false
	public boolean refundTwenty(int bid,String pwd,int uid){
		if(identificationCheck(bid,pwd)){	//商家身份验证通过
			return transferAcounts(bid,uid,20);	//退款20元
		}
		return false;
	}
	
	//查询用户钱包金额变动明细的接口实现
	public List getList(int uid,String pwd){
		if(identificationCheck(bid,pwd)){	//身份认证
			List list = new ArrayList<Blotter>();	//Blotter为表t_blotter对应的pojo
			//从数据库中查询该用户对应的流水记录
			Connection connection = getConnection();	
			String sql = "select * from t_blotter where master_id = ?";
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setInt(uid);
			ResultSet resultSet = statement.excuteQuery();
			//将流水记录转换成ArrayList
			while(resultSet.next()){
				//将每条记录转换成自定义Java类型Blotter
				Blotter blotter = new Blotter();
				blotter.set(resultSet.getObject("master_id",int.class));
				blotter.set(resultSet.getObject("client_id",int.class));
				blotter.set(resultSet.getObject("money",int.class));
				blotter.set(resultSet.getObject("time",String.class));
				//转换完一条记录,添加到list中
				list.add(blotter);
			}
			return list;
		}
		return null;
	}
}

class Blotter{
	private int masterId;
	private int clientId;
	private int money;
	private String time;
	
	public Blotter(){};
	
	public int getMasterId() {
        return masterId;
    }

    public void setMasterId(int masterId) {
        this.masterId = masterId;
    }

    public int getClientId() {
        return clientId;
    }

    public void setClientId(int clientId) {
        this.clientId = clientId;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }
}